from models.user import User
from PyQt5 import QtSql
from PyQt5.QtSql import QSqlQuery



database = QtSql.QSqlDatabase.addDatabase('QSQLITE')
database.setDatabaseName("../helloword.db")
database.open()


def new_user(username, password, email, exam):
    query = QSqlQuery()
    # query.prepare("INSERT INTO person (id, forename, surname) "
    #               "VALUES (:id, :forename, :surname)")
    register_user = User(username, password, email, exam)
    # insert_sql = "insert into user(uid,username,password,email,exam) values(\'"+username+'\''+','+'\''+register_user.password+'\''+','+'\''+email+'\''+','+'\''+str(exam)+'\''+')'
    query.prepare("insert into user(uid,username,password,email,exam) values(null,:username,:password,:email,:exam)")
    # query.bindValue(":uid",None)
    query.bindValue(":username", username)
    query.bindValue(":password", register_user.password)
    query.bindValue(":email", email)
    query.bindValue(":exam", exam)
    if query.exec_():
        print('succeed insert data')


def select_user_by_username(username):
    database.open()
    query = QSqlQuery()

    # query.bindValue(":username", username)
    if query.exec("SELECT * FROM user"): # 若存在则返回True
        print('succeed select data')
        print(query.value(0))




def select_user():
    pass


# import sqlite3
# def new_user(username,password,email,exam): # register
#     conn = sqlite3.connect("../helloword.db")
#     print("new user")
#     cursor = conn.cursor()
#     print(type(username),type(password),type(email),type(exam))
#     print(username,password,email,exam)
#     #insert_sql ="insert into user(username,password,email,exam) values(\'"+username+'\''+','+'\''+password+'\''+','+'\''+email+'\''+','+'\''+str(exam)+'\''+')'
#     user = User(username,password,email,exam)
#     # insert_sql ='''
#     # insert into user(username,password,email,exam) values (?,?,?,?)
#     # '''
#     # print(insert_sql)
#     insert_sql ="insert into user(username,password,email,exam) values (:username,:password,:email,:exam)"
#     # print(cursor.execute(insert_sql,(username,password,email,str(exam))))
#     cursor.execute(insert_sql,{"username":user.username,"password":user.password,"email":user.email,"exam":user.exam})
#     print("over")
#     cursor.close()
#     conn.commit()
#     conn.close()
#
# def select_user_by_username(username)->bool:
#     conn = sqlite3.connect("../helloword.db")
#     cursor = conn.cursor()
#     sql ="select * from user where username=:username"
#     cursor.execute(sql,{"username":username})
#     if len(cursor.fetchall()) ==0:
#         return False
#     else:
#         return True
#
#
#
# def select_user(username,password): # login verify
#     conn = sqlite3.connect("../helloword.db")
#     cursor = conn.cursor()
#     sql ="select * from user where username =:username and password=:password"
#     cursor.execute(sql,{"username":username,"password":password})
#     if len(cursor.fetchall()) ==0:
#         return False
#     else:
#         return True
#
#
import sqlite3
if __name__ == '__main__':
    username ="123"
    conn = sqlite3.connect("../helloword.db")
    cursor = conn.cursor()
    sql ="select * from user where username=\"123\""
    cursor.execute(sql)
    print(cursor.fetchall())
    # cursor.execute(sql,{"username":username})
    # print(cursor.description)
    # for row in cursor:
    #     self.showOneRow(row)

